package com.dcivision.dms.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.dcivision.dms.bean.DmsDocument;
import com.dcivision.dms.bean.DmsTreeNode;
import com.dcivision.framework.GlobalConstant;
import com.dcivision.framework.PermissionManager;
import com.dcivision.framework.SessionContainer;
import com.dcivision.framework.SystemParameterConstant;
import com.dcivision.framework.SystemParameterFactory;
import com.dcivision.framework.Utility;


public class DmsTreeDAObject {
  
  protected SessionContainer sessionContainer = null;
  
  protected Connection dbConn = null;
  
  public DmsTreeDAObject() {
  }
  
  public DmsTreeDAObject(SessionContainer sessionContainer, Connection dbConn) {
    this();
    this.sessionContainer = sessionContainer;
    this.dbConn = dbConn;
  }
  
  public Integer getTreeNodeParentId(Integer id) throws Exception {
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    try {
      pstmt = dbConn.prepareStatement("SELECT PARENT_ID FROM DMS_DOCUMENT WHERE (ID = ?)");
      pstmt.setInt(1, id.intValue());
      rs = pstmt.executeQuery();
      if (rs.next()) {
        return new Integer(rs.getInt("PARENT_ID"));
      }else {
        return null;
      }
    } catch (Exception ex) {
      throw ex;
    } finally {
      try { rs.close();} catch (Exception ignore) {} finally {rs = null;}
      try { pstmt.close();} catch (Exception ignore) {} finally {pstmt = null;}
    }
  }
  
  public List getDmsTreeNodeByIds(List Ids, Integer rootId) throws Exception {
    List lTreeNodes = new ArrayList();
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    try {
      StringBuffer sql = new StringBuffer();
      sql.append("SELECT ID, PARENT_ID, DOCUMENT_NAME, DOCUMENT_TYPE ");
      sql.append("FROM DMS_DOCUMENT ");
      sql.append("WHERE (RECORD_STATUS = ?) AND (ROOT_ID = ?) AND (ID IN ("+this.getStringFromIntegerList(Ids)+")) ");
      sql.append("ORDER BY DOCUMENT_TYPE, DOCUMENT_NAME");
      pstmt = dbConn.prepareStatement(sql.toString());
      pstmt.setString(1, GlobalConstant.RECORD_STATUS_ACTIVE);
      pstmt.setInt(2, rootId.intValue());
      rs = pstmt.executeQuery();
      while (rs.next()) {
        DmsTreeNode node = new DmsTreeNode();
        node.setId(new Integer(rs.getInt("ID")));
        node.setParentId(new Integer(rs.getInt("PARENT_ID")));
        node.setDocumentName(rs.getString("DOCUMENT_NAME"));
        node.setDocumentType(rs.getString("DOCUMENT_TYPE"));
        lTreeNodes.add(node);
      }
      return lTreeNodes;
    } catch (Exception ex) {
      throw ex;
    } finally {
      try { rs.close();} catch (Exception ignore) {} finally {rs = null;}
      try { pstmt.close();} catch (Exception ignore) {} finally {pstmt = null;}
    }
  }
  
  public List getDmsTreeNodeByParent(Integer parentId, Integer rootId) throws Exception {
    List pid = new ArrayList();
    pid.add(parentId);
    return this.getDmsTreeNodeByParents(pid, rootId);
  }
  
  public List getDmsTreeNodeByParents(List parentIds, Integer rootId) throws Exception {
    List lTreeNodes = new ArrayList();
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    try {
      StringBuffer sql = new StringBuffer();
      sql.append("SELECT ID, PARENT_ID, DOCUMENT_NAME, DOCUMENT_TYPE ");
      sql.append("FROM DMS_DOCUMENT ");
      sql.append("WHERE (RECORD_STATUS = ?) AND (DOCUMENT_TYPE IN (?, ?, ?, ?, ?)) AND (ROOT_ID = ?) AND PARENT_ID IN ("+this.getStringFromIntegerList(parentIds)+") ");
      if(!SystemParameterFactory.getSystemParameterBoolean(SystemParameterConstant.DMS_SHOW_EXPIRE_DOC)){
        sql.append(" AND (((EFFECTIVE_START_DATE IS NULL OR EFFECTIVE_START_DATE <=?) ");
        sql.append(" AND (EFFECTIVE_END_DATE IS NULL OR EFFECTIVE_END_DATE >?)) ");
        sql.append(" OR OWNER_ID = ?) ");
      }
      sql.append("ORDER BY DOCUMENT_TYPE, DOCUMENT_NAME");
      pstmt = dbConn.prepareStatement(sql.toString());
      pstmt.setString(1, GlobalConstant.RECORD_STATUS_ACTIVE);
      pstmt.setString(2, DmsDocument.FOLDER_TYPE);
      pstmt.setString(3, DmsDocument.COMPOUND_DOC_TYPE);
      pstmt.setString(4, DmsDocument.PAPER_DOC_TYPE);
      pstmt.setString(5, DmsDocument.FORM_DOC_TYPE);
      pstmt.setString(6, DmsDocument.FLOW_DOC_TYPE);
      
      pstmt.setInt(7, rootId.intValue());
      if(!SystemParameterFactory.getSystemParameterBoolean(SystemParameterConstant.DMS_SHOW_EXPIRE_DOC)){
        Timestamp currTime = Utility.getCurrentTimestamp();
        pstmt.setTimestamp(8, currTime);
        pstmt.setTimestamp(9, Utility.addDay(currTime, -1));
        pstmt.setInt(10, this.sessionContainer.getUserRecordID().intValue());
      }
      rs = pstmt.executeQuery();
      while (rs.next()) {
        DmsTreeNode node = new DmsTreeNode();
        node.setId(new Integer(rs.getInt("ID")));
        node.setParentId(new Integer(rs.getInt("PARENT_ID")));
        node.setDocumentName(rs.getString("DOCUMENT_NAME"));
        node.setDocumentType(rs.getString("DOCUMENT_TYPE"));
        
        lTreeNodes.add(node);
      }
      return lTreeNodes;
    } catch (Exception ex) {
      throw ex;
    } finally {
      try { rs.close();} catch (Exception ignore) {} finally {rs = null;}
      try { pstmt.close();} catch (Exception ignore) {} finally {pstmt = null;}
    }
  }
  
  public Map getForbiddenPermissionMapWhichNotInMatchRequired(
      String objectType, 
      List objectIds,
      List userRoleIds,
      List userGroupIds,
      Integer userRecordId) throws Exception {
    Map hmResult = new HashMap();
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    try {
      StringBuffer unionSQL = new StringBuffer();
      
      unionSQL.append("SELECT distinct OBJECT_ID ");
      unionSQL.append("FROM   USER_ROLE_PERMISSION ");
      unionSQL.append("WHERE MUST_FLAG='Y' AND OBJECT_TYPE='"+objectType+"' ");
      unionSQL.append("AND OBJECT_ID IN (" + this.getStringFromIntegerList(objectIds) + ") ");
      if (userRoleIds != null && userRoleIds.size() > 0) {
        unionSQL.append("AND USER_ROLE_ID NOT IN (" + this.getStringFromIntegerList(userRoleIds) + ") ");
      }
      unionSQL.append(" UNION ");
      unionSQL.append("SELECT distinct OBJECT_ID ");
      unionSQL.append("FROM   USER_GROUP_PERMISSION ");
      unionSQL.append("WHERE MUST_FLAG='Y' AND OBJECT_TYPE='"+objectType+"' ");
      unionSQL.append("AND OBJECT_ID IN (" + this.getStringFromIntegerList(objectIds) + ") ");
      if (userGroupIds != null && userGroupIds.size() > 0) {
        unionSQL.append("AND USER_GROUP_ID NOT IN (" + this.getStringFromIntegerList(userGroupIds) + ") ");
      }
      unionSQL.append(" UNION ");
      unionSQL.append("SELECT distinct OBJECT_ID ");
      unionSQL.append("FROM   USER_RECORD_PERMISSION ");
      unionSQL.append("WHERE MUST_FLAG='Y' AND OBJECT_TYPE='"+objectType+"' ");
      unionSQL.append("AND OBJECT_ID IN (" + this.getStringFromIntegerList(objectIds) + ") ");
      unionSQL.append("AND USER_RECORD_ID NOT IN (" + userRecordId.toString() + ") ");
      unionSQL.append("ORDER BY OBJECT_ID");

      pstmt = dbConn.prepareStatement(unionSQL.toString());
      rs = pstmt.executeQuery();
      
      while (rs.next()) {
        hmResult.put(objectType+""+rs.getInt("OBJECT_ID"), PermissionManager.DENIED_READ_PERMISSION);
      }
      return hmResult;
    } catch (Exception ex) {
      throw ex;
    } finally {
      try { rs.close();} catch (Exception ignore) {} finally {rs = null;}
      try { pstmt.close();} catch (Exception ignore) {} finally {pstmt = null;}
    }
  }
  
  public Map getDirectPermissionMap(
      String objectType, 
      List objectIds,
      List userRoleIds,
      List userGroupIds,
      Integer userRecordId) throws Exception {
    Map hmResult = new HashMap();
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    try {
      StringBuffer unionSQL = new StringBuffer();
      boolean hasAppend = false;
      if (userRoleIds != null && userRoleIds.size() > 0) {
        unionSQL.append("SELECT OBJECT_ID, PERMISSION ");
        unionSQL.append("FROM   USER_ROLE_PERMISSION ");
        unionSQL.append("WHERE OBJECT_TYPE='"+objectType+"' ");
        unionSQL.append("AND OBJECT_ID IN (" + this.getStringFromIntegerList(objectIds) + ") ");
        unionSQL.append("AND USER_ROLE_ID IN (" + this.getStringFromIntegerList(userRoleIds) + ") ");
        hasAppend = true;
      }

      if (userGroupIds != null && userGroupIds.size() > 0) {
        if (hasAppend) {
          unionSQL.append(" UNION ");
        }
        unionSQL.append("SELECT OBJECT_ID, PERMISSION ");
        unionSQL.append("FROM   USER_GROUP_PERMISSION ");
        unionSQL.append("WHERE OBJECT_TYPE='"+objectType+"' ");
        unionSQL.append("AND OBJECT_ID IN (" + this.getStringFromIntegerList(objectIds) + ") ");
        unionSQL.append("AND USER_GROUP_ID IN (" + this.getStringFromIntegerList(userGroupIds) + ") ");
        hasAppend = true;
      }
      if (hasAppend) {
        unionSQL.append(" UNION ");
      }
      unionSQL.append("SELECT OBJECT_ID, PERMISSION ");
      unionSQL.append("FROM   USER_RECORD_PERMISSION ");
      unionSQL.append("WHERE OBJECT_TYPE='"+objectType+"' ");
      unionSQL.append("AND OBJECT_ID IN (" + this.getStringFromIntegerList(objectIds) + ") ");
      unionSQL.append("AND USER_RECORD_ID IN (" + userRecordId.toString() + ") ");
      
      unionSQL.append("ORDER BY OBJECT_ID");

      pstmt = dbConn.prepareStatement(unionSQL.toString());

      rs = pstmt.executeQuery();
      while (rs.next()) {
        String oid = objectType+""+rs.getInt("OBJECT_ID");
        if (hmResult.containsKey(oid)) {
          String permission = PermissionManager.combinePermission(rs.getString("PERMISSION"), (String)hmResult.get(oid));
          hmResult.put(oid, permission);
        }else {
          hmResult.put(oid, rs.getString("PERMISSION"));
        }
      }
      return hmResult;
    } catch (Exception ex) {
      throw ex;
    }finally {
      try { rs.close();} catch (Exception ignore) {} finally {rs = null;}
      try { pstmt.close();} catch (Exception ignore) {} finally {pstmt = null;}
    }
  }
  
  public List getNotInheritPermissionObjectIds(String objectType, List objectIds) throws Exception {
    List resultIdList = new ArrayList();
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    try {
      StringBuffer selSQL = new StringBuffer();
      selSQL.append("SELECT OBJECT_ID ");
      selSQL.append("FROM   USER_PERMISSION_INHERIT ");
      selSQL.append("WHERE  OBJECT_TYPE = ? AND RECORD_STATUS = ? ");
      selSQL.append("AND OBJECT_ID IN ("+this.getStringFromIntegerList(objectIds)+")");
      pstmt = dbConn.prepareStatement(selSQL.toString());
      pstmt.setString(1, objectType);
      pstmt.setString(2, GlobalConstant.RECORD_STATUS_ACTIVE);
      rs = pstmt.executeQuery();
      while (rs.next()) {
        resultIdList.add(new Integer(rs.getInt("OBJECT_ID")));
      }
      return resultIdList;
    } catch (Exception ex) {
      throw ex;
    } finally {
      try { rs.close();} catch (Exception ignore) {} finally {rs = null;}
      try { pstmt.close();} catch (Exception ignore) {} finally {pstmt = null;}
    }
  }
  
  private String getStringFromIntegerList(List list) throws Exception {
    if (list.size()>0) {
      String str = list.get(0).toString();
      for (int i=1; i<list.size(); i++) {
        str += "," +list.get(i);
      }
      return str;
    }else {
      throw new Exception("List size is 0; Can not be parse to string.");
    }
  }
  
  
}